package com.clqteam.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clqteam.dao.submitOrderDao;
import com.clqteam.model.Product;
import com.clqteam.util.JDBCutil;

public class submitOrderDaoImpl implements submitOrderDao{

	@Override
	public double submitOrder(String code,String tbName,int vId,int oClassId,int eId,String oTime) {
		Connection con = JDBCutil.getConnection();
		//根据识别码(code)在order表中插入一条信息
		StringBuffer sql1 = new StringBuffer();
		sql1.append("INSERT INTO s_order(CODE) VALUE(?)");
		try {
			PreparedStatement pst = con.prepareStatement(sql1.toString());
			pst.setString(1, code);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//根据识别码取出自增长订单号(oId)
		int oId = 0;//订单号
		StringBuffer sql2 = new StringBuffer();
		sql2.append("SELECT s_order.oId FROM s_order");
		sql2.append(" WHERE s_order.code = ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql2.toString());
			pst.setString(1, code);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				oId = rs.getInt("oId");
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//将该订单号加入收支表
		StringBuffer sql12 = new StringBuffer();
		sql12.append("INSERT INTO s_accountant(oId) VALUES(?)");
		try {
			PreparedStatement pst = con.prepareStatement(sql12.toString());
			pst.setInt(1, oId);
			pst.executeUpdate();			
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		
		//根据员工号对应的临时表取出订单信息
		List<Product> pros = new ArrayList<Product>();
		StringBuffer sql3 = new StringBuffer();
		sql3.append("SELECT * FROM ");
		sql3.append(tbName);
		try {
			PreparedStatement pst = con.prepareStatement(sql3.toString());
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Product pro = new Product();
				pro.setpId(rs.getInt("ptId"));
				pro.setPoAmount(rs.getInt("ptAmount"));
				pros.add(pro);
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//根据会员ID获取折扣率和积分，积分转换率
		double discount = 1;//折扣率
		int point = 0;//积分
		double rate = 0;
		if(vId!=0){
			StringBuffer sql4 = new StringBuffer();
			sql4.append("SELECT s_vip.vDiscount,s_vip.vPoint,s_vip.pitRate");
			sql4.append(" FROM s_vip");
			sql4.append(" WHERE vId = ?");
			try {
				PreparedStatement pst = con.prepareStatement(sql4.toString());
				pst.setInt(1, vId);
				ResultSet rs = pst.executeQuery();
				if(rs.next()){
					discount = rs.getDouble("vDiscount");
					point = rs.getInt("vPoint");
					rate = rs.getDouble("pitRate");
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}		
		
		//将临时订单表中的信息写入订单详情表中	
		double oMoney = 0;//订单总价
		StringBuffer sql6 = new StringBuffer();
		sql6.append("INSERT INTO s_orderinfo(oId,pId,poAmount,poPrice) VALUE(?,?,?,?)");
		try {
			PreparedStatement pst = con.prepareStatement(sql6.toString());
			for(Product pro:pros){
				pst.setInt(1, oId);
				pst.setInt(2, pro.getpId());
				pst.setInt(3, pro.getPoAmount());
				//根据商品Id获取商品预售价
				StringBuffer sql7 = new StringBuffer();
				double price = 0;
				sql7.append("SELECT s_product.pPrice FROM s_product WHERE pId = ?");
				PreparedStatement pst0 = con.prepareStatement(sql7.toString());
				pst0.setInt(1, pro.getpId());
				ResultSet rs0 = pst0.executeQuery();
				if(rs0.next()){
					price = rs0.getDouble("pPrice");
				}
				double poPrice = price*discount;//单项总价
				pst.setDouble(4, poPrice);
				pst.executeUpdate();		
				oMoney += poPrice*pro.getPoAmount();
				//获取库存
				StringBuffer sql10 = new StringBuffer();
				int pAmount = 0;
				sql10.append("SELECT s_product.pAmount FROM s_product WHERE pId = ?");
				PreparedStatement pst2 = con.prepareStatement(sql10.toString());
				pst2.setInt(1, pro.getpId());
				ResultSet rs2 = pst2.executeQuery();
				if(rs2.next()){
					pAmount = rs2.getInt("pAmount");
				}
				//减去库存
				StringBuffer sql9 = new StringBuffer();
				sql9.append("UPDATE s_product SET s_product.pAmount = ? WHERE pId = ?");
				PreparedStatement pst1 = con.prepareStatement(sql9.toString());
				pst1.setInt(1, pAmount-pro.getPoAmount());
				pst1.setInt(2, pro.getpId());
				pst1.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		//增加会员积分
		if(vId!=0){
			StringBuffer sql11 = new StringBuffer();
			sql11.append("UPDATE s_vip SET s_vip.vPoint = ? WHERE s_vip.vId = ?");
			try {
				PreparedStatement pst = con.prepareStatement(sql11.toString());
				pst.setInt(1, point+ (int)Math.round(oMoney*rate));
				pst.setInt(2, vId);
				pst.executeUpdate();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		//根据订单详情表，填写订单表
		StringBuffer sql8 = new StringBuffer();
		sql8.append("UPDATE s_order SET oClassId=?,oMoney=?,eId=?,oTime=?,vId=?");
		sql8.append(" WHERE oId=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql8.toString());
			pst.setInt(1, oClassId);
			pst.setDouble(2, oMoney);
			pst.setInt(3, eId);
			pst.setString(4, oTime);
			pst.setInt(5, vId);
			pst.setInt(6, oId);
			pst.executeUpdate();
			JDBCutil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return oMoney;
	}

}
